insert overwrite table jms_dm.dm_dispatch_staff_count  partition ( dt )
select 
    to_date(sign_time) as date_time,
    dispatch_network_code,
    dispatch_network_name,
    dispatch_staff_code,
    dispatch_staff_name,
    dispatch_finance_code,
    dispatch_finance_name,
    franchisee_code,
    franchisee_name,
    count(waybill_no) as bill_count,
    sum(case when is_refund=1 or is_refund=3 then 1 else 0 end ) as refund_count,
    sum(charge_weight) as charge_weight,
    to_date(sign_time) as dt
from jms_dwd.dwd_yl_oms_dispatch_waybill_base_dt t 
where t.dt>='{{ execution_date | date_add(-60) | cst_ds }}'  and t.dt<='{{ execution_date  | cst_ds }}'
and to_date(sign_time) >='{{ execution_date | date_add(-30) | cst_ds }}'  and to_date(sign_time)<='{{ execution_date | date_add(-3) | cst_ds }}'
and datediff(to_date(sign_time),to_date(create_time)) < 30

and sign_time is not null
    group by 
    to_date(sign_time),
    dispatch_finance_code,
    dispatch_finance_name,
    franchisee_code,
    franchisee_name,
    dispatch_network_code,
    dispatch_network_name,
    dispatch_staff_code,
    dispatch_staff_name
    
 distribute by pmod(hash(rand()), 3)
 ;
